import pandas as pd

# 1. Load the workbook
file_path = "Monthly_Indices_50_Media.xlsx"
xls = pd.ExcelFile(file_path, engine="openpyxl")

# 2. Prepare a list to collect each media's metrics
records = []

# 3. Loop over each sheet (media outlet)
for media in xls.sheet_names:
    # 3.1 Read the necessary columns
    df = pd.read_excel(
        xls,
        sheet_name=media,
        usecols=["Month", "TotalMentions", "TotalEvents"]
    )

    # 3.2 Parse "Month" as a pandas Period (YYYY-MM)
    df['Month'] = pd.to_datetime(df['Month'], format='%Y-%m').dt.to_period('M')

    # 3.3 Compute the average of the monthly TotalMentions
    #      (this is the mean of the per-month mentions)
    monthly_mean = df.groupby('Month')['TotalMentions'].mean()
    avg_monthly_mentions = monthly_mean.mean()

    # 3.4 Compute the event mean: (sum of mentions) / (sum of events)
    total_mentions = df['TotalMentions'].sum()
    total_events = df['TotalEvents'].sum()
    event_mean = total_mentions / total_events if total_events != 0 else float('nan')

    # 3.5 Store the results
    records.append({
        "Media": media,
        "AvgMonthlyMentions": avg_monthly_mentions,
        "EventMean": event_mean
    })

# 4. Build the summary DataFrame
summary_df = pd.DataFrame(records).set_index("Media")

# 5. Write out to Excel
output_file = "Media_Mention_Summary.xlsx"
summary_df.to_excel(output_file)

print(f"✅ Summary metrics saved to '{output_file}'")
